"""
Case Type   : 事务-ustore
Case Name   : 创建所需要表
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建schema及表
    2.插入数据
Expect      :
    1.成功
    2.成功
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.schema = 'schema_ustore_trans'
        self.table = 't_ustore_trans_0001'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)

    def testunit_1(self):
        text = '----step1:创建schema及表;expect:成功----'
        self.log.info(text)
        q_str = f'''drop schema if exists schema_ustore_trans cascade;
                    create schema {self.schema};
                    create table {self.schema}.{self.table}(
                    c_id int, c_d_id int not null,
                    c_w_id int not null,
                    c_first varchar(16) not null,
                    c_middle char(2),
                    c_last varchar(16) not null,
                    c_street_1 varchar(20) not null,
                    c_street_2 varchar(20),
                    c_city varchar(20) not null,
                    c_state char(2) not null,
                    c_zip char(9) not null,
                    c_phone char(16) not null,
                    c_since timestamp,
                    c_credit char(2) not null,
                    c_credit_lim numeric(12,2),
                    c_discount numeric(4,4),
                    c_balance numeric(12,2),
                    c_ytd_payment real not null,
                    c_payment_cnt number not null,
                    c_delivery_cnt bool not null,
                    c_end date not null,
                    c_vchar varchar(1000),
                    c_data clob,
                    primary key (c_id,c_d_id,c_w_id)) with 
                    (storage_type=ustore);'''
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertIn(self.constant.CREATE_SCHEMA_SUCCESS_MSG, q_res,
                      '执行失败: ' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res,
                      '执行失败: ' + text)

        text = '--step2:插入数据 expect:成功--'
        insert_sql = f"insert into {self.schema}.{self.table} " \
                     f"values(1,1,1,'is'||'1'||'cmRDs'||'1','OE','BAR'||'1'||" \
                     f"'Bar'||'1','bkili'||'1'||'fcrRGF'||'1','pmbwo'||'1'||" \
                     f"'vhSDGJ'||'1','dyf'||'1'||'rDa'||'1','uq',4801||'1'," \
                     f"940||'1'||215||'1'," \
                     f"to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')," \
                     f"'GC',50000.0,0.4361328,-10.0,10.0,1,true," \
                     f"to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')," \
                     f"'QVLDETANRBRBURBMfhlhfrcllgfSMNTECC348493214893542NPFZ" \
                     f"CSfjlufvCDSFQVLDETANRBRBURBMfhlhfrcllgfSMNTECC3484932" \
                     f"14893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMfhlhfrcllgfSM" \
                     f"NTECC348493214893542NPFZCSfjlufvCDSFQVLDETANRBRBURBMf" \
                     f"hlhfrcllgfSMNTECC348493214893542NPFZCSfjlufvCDSFQVLDE" \
                     f"TANRBRBURBMfhlhfrcllgfSMNTECC348493214893542NPFZCSfjl" \
                     f"ufvCDSFQVLDETANRBRBURBMfhlhfrcllgfSMNTECC348493214893" \
                     f"542NPFZCSfjlufvCDSF'||'1','12343545876431234552134456" \
                     f"56723123424554566776763221132454566768767433242323445" \
                     f"453565654542323');" \
                     f"begin" \
                     f"  forall i in 1..1000" \
                     f"    insert into {self.schema}.{self.table} " \
                     f"    select c_id+i,c_d_id+i,c_w_id+i,'isi'||i,c_middle," \
                     f"    'bar'||i,c_street_1,c_street_2,c_city,c_state," \
                     f"    c_zip,c_phone,c_since,c_credit,c_credit_lim," \
                     f"    c_discount,c_balance,c_ytd_payment," \
                     f"    c_payment_cnt,c_delivery_cnt,c_end,c_vchar," \
                     f"    c_data from {self.schema}.{self.table} " \
                     f"    where c_id=1;" \
                     f"end;"
        self.log.info(insert_sql)
        insert_res = self.pri_sh.execut_db_sql(insert_sql)
        self.log.info(insert_res)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG,
                      insert_res, '执行失败' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      insert_res, '执行失败' + text)

    def tearDown(self):
        text = '----无需清理环境----'
        self.log.info(text)
        self.log.info(f'---{os.path.basename(__file__)} end---')

